package com.tangdi.Dao.impl;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_DWXX;
import com.tangdi.Bean.CZGZ_GZLB;
import com.tangdi.Bean.CZGZ_SJGZ;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.common.DBConnect;

public class GongziInsertImpl {
	private Logger logger = Logger.getLogger(GongziInsertImpl.class);
	Connection con = DBConnect.getInstance().getConnection();
	PreparedStatement pstmt=null;
	ResultSet rs=null;

	//删除工资明细表CZGZ_ZGGZMX, 各单位工资发放汇总表CZGZ_DWXX, 工资类别表CZGZ_GZLB, 实际工资科目表CZGZ_SJGZ
	public int deleteTableData(String month, String tableName){
		 //String s_name = "SEQ_NO,TP_BAT_NO,BAT_NO,THD_BAT_NO,AP_SYS_CDE,PAY_BNK,PER_BNK_NO,BNK_FLG,PER_AC,PER_OLD_AC,NME,TXN_AMT,REL_TXN_AMT,DTL_CDE,DTL_MSG,DTL_STS,BR,TLR,BUS_KND,BUS_TYP,PWD_CHK_FLG,TXN_DTE,BSMR,YF,DW_DM,DW_MC,GZ_LB_DM,BM_DM,RY_DM,ORD_NO,KM_MC,ID_NO,ACCOUNT_FLG,YF_GZ,DK_XJ,SF_TXN_AMT,GZ_LX,GZ_LX_MC,PAY_SEQ_NO,PAY_LOG_NO,RMK1,RMK2,B001,B002,B003,B004,B005,B006,B007,B008,B009,B010,B011,B012,B013,B014,B015,B016,B017,B018,B019,B020,B021,B022,B023,B024,B025,B026,B027,B028,B029,B030,B031,B032,B033,B034,B035,B036,B037,B038,B039,B040,B041,B042,B043,B044,B045,B046,B047,B048,B049,B050,B051,B052,B053,B054,B055,B056,B057,B058,B059,B060,B061,B062,B063,B064,B065,B066,B067,B068,B069,B070,B071,B072,B073,B074,B075,B076,B077,B078,B079,B080,B081,B082,B083,B084,B085,B086,B087,B088,B089,B090,FFFS,DHYB,DHYH,DHDZ,DHKH,JT_TXN_AMT,JT_CUS_AC,JT_SALARY_YEAR,JT_SALARY_MONTH,GZ_XZ,S_SEQ_NO,RCN_CDE";
		String sql_del="DELETE FROM "+ tableName;// +" WHERE YF = '"+ month +"'";
		logger.info(sql_del);
		try{
			pstmt=con.prepareStatement(sql_del);
			pstmt.executeUpdate();
			con.commit();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
			}finally{
				if(pstmt!=null){
					try{
						pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						return -2;
					}
				}
			}
		return 0;
	}

	//只保留两个月的，超过两个月的全部删掉，确保不影响使用
	public int delBeforeTwoMonth(String month_before_2m, String tableName){
		 //String s_name = "SEQ_NO,TP_BAT_NO,BAT_NO,THD_BAT_NO,AP_SYS_CDE,PAY_BNK,PER_BNK_NO,BNK_FLG,PER_AC,PER_OLD_AC,NME,TXN_AMT,REL_TXN_AMT,DTL_CDE,DTL_MSG,DTL_STS,BR,TLR,BUS_KND,BUS_TYP,PWD_CHK_FLG,TXN_DTE,BSMR,YF,DW_DM,DW_MC,GZ_LB_DM,BM_DM,RY_DM,ORD_NO,KM_MC,ID_NO,ACCOUNT_FLG,YF_GZ,DK_XJ,SF_TXN_AMT,GZ_LX,GZ_LX_MC,PAY_SEQ_NO,PAY_LOG_NO,RMK1,RMK2,B001,B002,B003,B004,B005,B006,B007,B008,B009,B010,B011,B012,B013,B014,B015,B016,B017,B018,B019,B020,B021,B022,B023,B024,B025,B026,B027,B028,B029,B030,B031,B032,B033,B034,B035,B036,B037,B038,B039,B040,B041,B042,B043,B044,B045,B046,B047,B048,B049,B050,B051,B052,B053,B054,B055,B056,B057,B058,B059,B060,B061,B062,B063,B064,B065,B066,B067,B068,B069,B070,B071,B072,B073,B074,B075,B076,B077,B078,B079,B080,B081,B082,B083,B084,B085,B086,B087,B088,B089,B090,FFFS,DHYB,DHYH,DHDZ,DHKH,JT_TXN_AMT,JT_CUS_AC,JT_SALARY_YEAR,JT_SALARY_MONTH,GZ_XZ,S_SEQ_NO,RCN_CDE";
		String sql_del="DELETE FROM "+ tableName +" WHERE YF < '"+ month_before_2m +"'";
		logger.info(sql_del);
		try{
			pstmt=con.prepareStatement(sql_del);
			pstmt.executeUpdate();
			con.commit();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
			}finally{
				if(pstmt!=null){
					try{
						pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						return -2;
					}
				}
			}
		return 0;
	}
	
	public int addGZMX(List<CZGZ_ZGGZMX> gzmx_list, Field[] field){
		 StringBuffer sql=new StringBuffer("INSERT INTO CZGZ_ZGGZMX(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append(field[i].getName() + ",");
		 }
		 sql.deleteCharAt(sql.length() - 1);
		 sql.append(")  VALUES(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append("?,");
		 }
		 sql.replace(sql.length() - 1, sql.length(), ")");
		try{
			pstmt=con.prepareStatement(sql.toString());
			for(int i = 0; i < gzmx_list.size(); i++){
				CZGZ_ZGGZMX gzmx = new CZGZ_ZGGZMX();
				gzmx = gzmx_list.get(i);
				for(int j=0 ; j<field.length ; j++){
					String value_gz = (String)Utils.getFieldReflect(gzmx, CZGZ_ZGGZMX.class, field[j].getName());
					 if(!value_gz.equals("null")){
						 pstmt.setString(j + 1, value_gz);
					 }else{
						 pstmt.setString(j + 1, null);
					 }
				 }
				pstmt.addBatch();
				
				if((i != 0) && (i % 2000 == 0)){
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
				}
			}
			 pstmt.executeBatch();
			 con.commit();
			 con.close();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch(SQLException e){
					e.printStackTrace();
					return -2;
				}
			}
		}
		return 0;
	}
	
	
	public int addGZDWXX(List<CZGZ_DWXX> gzdw_list, Field[] field){
		 StringBuffer sql=new StringBuffer("INSERT INTO CZGZ_DWXX(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append(field[i].getName() + ",");
		 }
		 sql.deleteCharAt(sql.length() - 1);
		 sql.append(")  VALUES(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append("?,");
		 }
		 sql.replace(sql.length() - 1, sql.length(), ")");
		try{
			pstmt=con.prepareStatement(sql.toString());
			for(int i = 0; i < gzdw_list.size(); i++){
				CZGZ_DWXX gzdw_1 = new CZGZ_DWXX();
				gzdw_1 = gzdw_list.get(i);
				for(int j=0 ; j<field.length ; j++){
					String value_gz = (String)Utils.getFieldReflect(gzdw_1, CZGZ_DWXX.class, field[j].getName());
					 if(!value_gz.equals("null")){
						 pstmt.setString(j + 1, value_gz);
					 }else{
						 pstmt.setString(j + 1, null);
					 }
				 }
				pstmt.addBatch();
				
				if((i != 0) && (i % 2000 == 0)){
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
				}
			}
			 pstmt.executeBatch();
			 con.commit();
			 con.close();
			 
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch(SQLException e){
					e.printStackTrace();
					return -2;
				}
			}
		}
		return 0;
	}
	
	
	public int addGZLB(List<CZGZ_GZLB> gzlb_list, Field[] field){
		 StringBuffer sql=new StringBuffer("INSERT INTO CZGZ_GZLB(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append(field[i].getName() + ",");
		 }
		 sql.deleteCharAt(sql.length() - 1);
		 sql.append(")  VALUES(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append("?,");
		 }
		 sql.replace(sql.length() - 1, sql.length(), ")");
		try{
			pstmt=con.prepareStatement(sql.toString());
			for(int i = 0; i < gzlb_list.size(); i++){
				CZGZ_GZLB gzlb_1 = new CZGZ_GZLB();
				gzlb_1 = gzlb_list.get(i);
				for(int j=0 ; j<field.length ; j++){
					String value_gz = (String)Utils.getFieldReflect(gzlb_1, CZGZ_GZLB.class, field[j].getName());
					 if(!value_gz.equals("null")){
						 pstmt.setString(j + 1, value_gz);
					 }else{
						 pstmt.setString(j + 1, null);
					 }
				 }
				pstmt.addBatch();
				
				if((i != 0) && (i % 2000 == 0)){
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
				}
			}
			 pstmt.executeBatch();
			 con.commit();
			 con.close();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch(SQLException e){
					e.printStackTrace();
					return -2;
				}
			}
		}
		return 0;
	}

	
	public int addSJGZ(List<CZGZ_SJGZ> sjgz_list, Field[] field){
		 StringBuffer sql=new StringBuffer("INSERT INTO CZGZ_SJGZ(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append(field[i].getName() + ",");
		 }
		 sql.deleteCharAt(sql.length() - 1);
		 sql.append(")  VALUES(");
		 for(int i=0 ; i<field.length ; i++){
			 sql.append("?,");
		 }
		 sql.replace(sql.length() - 1, sql.length(), ")");
		try{
			pstmt=con.prepareStatement(sql.toString());
			for(int i = 0; i < sjgz_list.size(); i++){
				CZGZ_SJGZ sjgz_1 = new CZGZ_SJGZ();
				sjgz_1 = sjgz_list.get(i);
				for(int j=0 ; j<field.length ; j++){
					String value_gz = (String)Utils.getFieldReflect(sjgz_1, CZGZ_SJGZ.class, field[j].getName());
					 if(!value_gz.equals("null")){
						 pstmt.setString(j + 1, value_gz);
					 }else{
						 pstmt.setString(j + 1, null);
					 }
				 }				
				pstmt.addBatch();
				
				if((i != 0) && (i % 2000 == 0)){
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
				}
			}
			 pstmt.executeBatch();
			 con.commit();
			 con.close();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch(SQLException e){
					e.printStackTrace();
					return -2;
				}
			}
		}
		return 0;
	}
}
